package Data;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import Interface_Data.Course;
import Interface_Data.CourseDataI;
import Interface_Data.GetDataforAlgorithmI;
import Interface_Data.Stu;
import Interface_Data.UserDataI;
import Util.DBUtil;
import Vo.CourseChooseVo;
import Vo.CourseList;
import Vo.CourseVo;
import Vo.Course_alg;
import Vo.StuVo;
import Vo.Stu_alg;

public class GetDataforAlgorithm implements GetDataforAlgorithmI {
	DBUtil dbUtil = new DBUtil();
	Connection con = null;
   CourseDataI courseDataI = new CourseData();	
	@Override
	public ArrayList<Stu> returnStuList(String model) {
		// TODO Auto-generated method stub
		ArrayList<Stu> stuList = new ArrayList<Stu>();
		ArrayList<String> stuIdList = this.returnStuIdList();

		for (int i = 0; i < stuIdList.size(); i++) {
			try {
				UserDataI userDataI = new UserData();
				StuVo stuVo = userDataI.returnStuInfo(stuIdList.get(i));
				int choosenScore = this.returnChoosenScore(stuIdList.get(i), model);
				stuList.add(new Stu_alg(stuVo.getId(),stuVo.getGrade(),returnChoosenCourseList(stuIdList.get(i), model),
						                  stuVo.getModel1Score()+returnChoosenScore(stuIdList.get(i), model)));
			} catch (Exception e) {
				// TODO Auto-generated catch block
			}

		}
		return stuList;
	}
	
	private boolean isTheModel(String model,String courseType){
		String sql = "Select * from wholeframe where courseModel =?";
		try {
			con = dbUtil.getDataCon();

			PreparedStatement ptmt = con.prepareStatement(sql);
			ptmt.setString(1,model );
			ResultSet rs = ptmt.executeQuery();
			while (rs.next()) {
				if(courseType.equals(rs.getString("catagory"))){
					return true;
				}
			}

			return false;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				dbUtil.closeConnection(con);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return false;
	}
    
	private int returnChoosenScore(String stuId,String Model){
		String sql = "Select * from courselist c,coursechooselist cc where c.id = cc.courseId and cc.stuId = ?";
		try {
			con = dbUtil.getDataCon();

			PreparedStatement ptmt = con.prepareStatement(sql);
			ptmt.setString(1,stuId );
			ResultSet rs = ptmt.executeQuery();

			int score = 0;

			while (rs.next()) {
				if(isTheModel(Model, rs.getString("c.courseType"))){
					score += rs.getInt("c.score");
				}
			}

			return score;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				dbUtil.closeConnection(con);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

		return 0;
		
	}
	
	private ArrayList<Integer> returnChoosenCourseList(String stuId,String model){
		String sql = "Select * from csrecord c,courselist cl where c.courseId = cl.id and c.stuId=?";
		try {
			con = dbUtil.getDataCon();

			PreparedStatement ptmt = con.prepareStatement(sql);
			ptmt.setString(1,stuId );
			ResultSet rs = ptmt.executeQuery();

			ArrayList<Integer> ChoosenCourseList = new ArrayList<Integer>();

			while (rs.next()) {
				if(this.isTheModel(model, rs.getString("cl.courseType"))){
				   ChoosenCourseList.add(rs.getInt("c.courseId"));
				   courseDataI.deleteChooseRecord(new CourseChooseVo(rs.getInt("c.courseId"), rs.getString("stuId"),1, 1));
				}				
			}

			return ChoosenCourseList;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				dbUtil.closeConnection(con);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

		return null;
		
	}
	
	private ArrayList<String> returnStuIdList() {
		String sql = "Select * from csrecord";
		try {
			con = dbUtil.getDataCon();

			PreparedStatement ptmt = con.prepareStatement(sql);
			ResultSet rs = ptmt.executeQuery();

			ArrayList<String> stuIdList = new ArrayList<String>();

			while (rs.next()) {
				String stuId = rs.getString("stuId");
				boolean isrepeat = false;
				for (int i = 0; i < stuIdList.size(); i++) {
					if (stuId.equals(stuIdList.get(i))) {
						isrepeat = true;
						break;
					}
				}
				if (!isrepeat) {
					stuIdList.add(stuId);
				}

			}

			return stuIdList;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				dbUtil.closeConnection(con);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
     	return null;
	}

	@Override
	public ArrayList<Course> returnCourseList(String model) {
		// TODO Auto-generated method stub
		try{
			CourseDataI courseDataI = new CourseData();
			CourseList courseList = courseDataI.returnCourseList(model);
			ArrayList<Course> courseAlgList = new ArrayList<Course>();
			for(int i = 0 ; i <  courseList.getSize() ; i ++){
			  CourseVo courseVo = courseList.getCourse(i);
			  courseAlgList.add(new Course_alg(courseVo.getId(),courseVo.getQuota()-courseVo.getSelected(),courseVo.getScore()));
			}
			return courseAlgList;
		}catch(Exception e){
			
		}
		return null;
	}
}